﻿
GO

/****** Object:  Table [dbo].[CMS_Contents]    Script Date: 06/25/2012 23:20:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CMS_Contents](
	[ContentID] [int] IDENTITY(1,1) NOT NULL,
	[ContentLangID] [int] NULL,
	[ContentVersionID] [int] NOT NULL,
	[VersionID] [int] NOT NULL,
	[ZoneID] [int] NOT NULL,
	[Headline] [nvarchar](250) NULL,
	[SubHeadline] [nvarchar](250) NULL,
	[Teaser] [nvarchar](3000) NULL,
	[Avatar] [nvarchar](350) NULL,
	[AvatarTeaser] [nvarchar](250) NULL,
	[SmallAvatar] [nvarchar](350) NULL,
	[BigAvatar] [nvarchar](350) NULL,
	[AttachFile] [nvarchar](350) NULL,
	[Status] [int] NULL,
	[Body] [ntext] NULL,
	[PrimaryAuthor] [int] NULL,
	[SecondaryAuthors] [nvarchar](350) NULL,
	[CreatedBy] [int] NULL,
	[CreatedOn] [datetime] NULL,
	[ModifiedBy] [int] NULL,
	[ModifiedOn] [datetime] NULL,
	[DeletedOn] [datetime] NULL,
	[DeletedBy] [int] NULL,
	[DatetimePublish] [datetime] NULL,
	[UserPublish] [int] NULL,
	[DatetimeRemove] [datetime] NULL,
	[UserRemove] [int] NULL,
	[ShowType] [int] NULL,
	[Lang] [int] NULL,
	[HitCount] [int] NULL,
	[URL] [nvarchar](500) NULL,
	[KeyContents] [nvarchar](300) NULL,
	[ReturnBody] [nvarchar](1024) NULL,
	[Locked] [nvarchar](50) NULL,
	[HitCountComments] [int] NULL,
	[Author] [nvarchar](350) NULL,
	[Check_type] [int] NULL,
 CONSTRAINT [PK_CMS_Contents] PRIMARY KEY CLUSTERED 
(
	[ContentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tieu de' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'Headline'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tom tat noi dung' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'Teaser'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Anh dai dien' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'Avatar'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Mo ta anh' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'AvatarTeaser'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Anh nho' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'SmallAvatar'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Anh Lon' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'BigAvatar'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Noi dung chinh' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'Body'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User tao moi' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'CreatedBy'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Mgay tao moi' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'CreatedOn'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User sua' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'ModifiedBy'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ngay sua' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'ModifiedOn'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ngay xoa' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'DeletedOn'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User Xoa' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'DeletedBy'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ngay xuat ban' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'DatetimePublish'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User xuat ban' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'UserPublish'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ngay go xuong' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'DatetimeRemove'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User go xuong' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'UserRemove'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Hien thi trang chủ,nổi bật,...' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'ShowType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1->VI-VN;2->En-US' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'Lang'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nội dung trả lại' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'ReturnBody'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Khóa bài viết' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CMS_Contents', @level2type=N'COLUMN',@level2name=N'Locked'
GO

ALTER TABLE [dbo].[CMS_Contents] ADD  CONSTRAINT [DF_CMS_Contents_VersionID]  DEFAULT ((1)) FOR [VersionID]
GO



GO

/****** Object:  StoredProcedure [dbo].[CMS_Contents_GetTotal]    Script Date: 06/25/2012 23:20:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--[CMS_Contents_GetTotal]'5','1','0','','0','12/12/2005','12/12/2010'
Create PROCEDURE [dbo].[CMS_Contents_GetTotal]
	@Status int=null,
	@lang int=null,
	@ZoneID int=null,
	@Keyword nvarchar(200)=null,
	@Createdby int=null,
	@StartDate datetime=null,
	@EndDate datetime=null
AS
	
	DECLARE @Select NVARCHAR(500)
	DECLARE @Key NVARCHAR(500)
	DECLARE @Table NVARCHAR(500)
	DECLARE @FromClause NVARCHAR(500)
	DECLARE @WhereClause NVARCHAR(500)
	
	SET @Select = 'SELECT count(distinct CMS_Contents.contentlangid) ' 
	SET @Key = ' ContentLangID '
	SET @Table = 'CMS_Contents'
	
	SET @FromClause = ' FROM CMS_Contents,CMS_Zones,Core_Users '	
	SET @WhereClause=' WHERE CMS_Zones.ZoneID=CMS_Contents.ZoneID AND CMS_Contents.CreatedBy=Core_Users.UserID AND CMS_Contents.Status=' + CAST(@Status AS VARCHAR)		
	SET @WhereClause= @WhereClause + ' AND CMS_Contents.Status=' + CAST(@Status AS VARCHAR)		
	SET @WhereClause =@WhereClause + ' AND CMS_Contents.ModifiedOn Between '+quotename(@StartDate,'''' )+' AND '+quotename(@EndDate,'''' ) +'  '
	if(@Createdby>0)
		SET @WhereClause=@WhereClause  + ' AND CMS_Contents.CreatedBy=' + CAST(@Createdby AS VARCHAR)
	if(@ZoneID>0)
		SET @WhereClause=@WhereClause  + ' AND CMS_Contents.ZoneID=' + CAST(@ZoneID AS VARCHAR)
	if(@Keyword <> '')
		SET @WhereClause = @WhereClause + ' AND CMS_Contents.Headline LIKE N' + quotename('%' + @Keyword + '%','''') 

	DECLARE @Sql NVARCHAR(2000)
	
	SET @Sql = @Select + @FromClause + @WhereClause 
	
	EXEC(@Sql)



GO

/****** Object:  StoredProcedure [dbo].[CMS_Contents_SearchAll]    Script Date: 06/25/2012 23:20:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




--[CMS_Contents_SearchAll]'5','1','0','','0','12/12/2005','12/12/2010','0','20'
Create PROCEDURE [dbo].[CMS_Contents_SearchAll]
	@Status int=null,
	@lang int=null,
	@ZoneID int=null,
	@Keyword nvarchar(200)=null,
	@CreatedBy int=null,
	@StartDate datetime=null,
	@EndDate datetime=null,
	@From int=null,
	@To int=null
AS
IF(@From < 1) SET @From = 1
	DECLARE @Select NVARCHAR(4000)
	DECLARE @Key NVARCHAR(500)
	DECLARE @Table NVARCHAR(500)
	DECLARE @FromClause NVARCHAR(500)
	DECLARE @WhereClause NVARCHAR(1000)
	DECLARE @OrderClause NVARCHAR(500)	
	SET @Select = 'select distinct CMS_Contents.contentlangid,CMS_Contents.KeyContents,CMS_Contents.CreatedOn,CMS_Contents.ContentID,CMS_Contents.ModifiedOn,CMS_Contents.Headline,CMS_Contents.Status ,CMS_Zones.ZoneID,CMS_Zones.Title,Core_Users.Username ' 		
	SET @Key = ' ContentID '
	SET @Table = 'CMS_Contents'
	SET @OrderClause = ' ORDER BY CMS_Contents.ContentID DESC '
	
	SET @FromClause = 'FROM CMS_Contents,CMS_Zones,Core_Users'
	SET @WhereClause=' WHERE CMS_Zones.ZoneID=CMS_Contents.ZoneID AND CMS_Contents.CreatedBy=Core_Users.UserID '	
	SET @WhereClause= @WhereClause + ' AND CMS_Contents.Status=' + CAST(@Status AS VARCHAR)		
	SET @WhereClause =@WhereClause + ' AND CMS_Contents.ModifiedOn Between '+quotename(@StartDate,'''' )+' AND '+quotename(@EndDate,'''' ) +'  '
	if(@Createdby>0)
		SET @WhereClause=@WhereClause  + ' AND CMS_Contents.CreatedBy=' + CAST(@Createdby AS VARCHAR)
	if(@ZoneID>0)
		SET @WhereClause=@WhereClause  + ' AND CMS_Contents.ZoneID=' + CAST(@ZoneID AS VARCHAR)
	if(@Keyword <> '')
		SET @WhereClause = @WhereClause + ' AND CMS_Contents.Headline LIKE N' + quotename('%' + @Keyword + '%','''') 

	DECLARE @Sql NVARCHAR(2000)
	
	SET @Sql = @Select + @FromClause + @WhereClause + ' AND ' + @Key + ' IN '
	
	SET @Sql = @Sql + '(SELECT TOP ' + cast((@To-@From + 1) AS VARCHAR) + '' + @Key + @FromClause + @WhereClause

	SET @Sql = @Sql + + ' AND ' + @Key + ' NOT IN '

	SET @Sql = @Sql + '(SELECT TOP ' + cast(@From - 1 AS VARCHAR) + '' + @Key + @FromClause + @WhereClause
		
	IF( @OrderClause <> '')
		SET @Sql = @Sql + @OrderClause + ') ' + @OrderClause + ') ' + @OrderClause
	ELSE
		SET @Sql = @Sql + '))'
	exec(@Sql)






GO

/****** Object:  StoredProcedure [dbo].[CMS_Contents_UpdateStatus]    Script Date: 06/25/2012 23:20:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[CMS_Contents_UpdateStatus]
@ContentID nvarchar(255)=null,
@check int=null
AS
declare @strId nvarchar(255)
-- Bài viết đang soạn
if  @check =2
Begin	
	Set @strId = 'Update CMS_Contents SET Status=3 where ContentID In ' + @ContentID
end
--Bài viết ở trạng thái chờ duyệt
if  @check =3
Begin
	Set @strId = 'Update CMS_Contents SET Status=5 where ContentID In ' + @ContentID
end 
--Bài viết ở trạng thái đăng trên site
if  @check =5
Begin
	Set @strId = 'Update CMS_Contents SET Status=6 where ContentID In ' + @ContentID
end 
--Khôi phục lại bài viết->chuyển bài viết về trạng thái chờ duyệt
if  @check =6
Begin
	Set @strId = 'Update CMS_Contents SET Status=3 where ContentID In ' + @ContentID
end 
Execute (@strId)


GO

/****** Object:  StoredProcedure [dbo].[CMS_Contents_Update]    Script Date: 06/25/2012 23:20:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[CMS_Contents_Update]
(
	@ContentID int,
	@ContentLangID int,
	@ContentVersionID int,
	@VersionID int,
	@ZoneID int,
	@Headline nvarchar(250),
	@SubHeadline nvarchar(250),
	@Teaser nvarchar(3000),
	@Avatar nvarchar(350),
	@AvatarTeaser nvarchar(250),
	@SmallAvatar nvarchar(350),
	@BigAvatar nvarchar(350),
	@AttachFile nvarchar(350),	
	@Body ntext,
	@PrimaryAuthor int,
	@SecondaryAuthors nvarchar(350),		
	@ModifiedBy int,
	@ModifiedOn datetime,
	@DeletedOn datetime,
	@DeletedBy int,
	@DatetimePublish datetime,
	@UserPublish int,
	@DatetimeRemove datetime,
	@UserRemove int,
	@ShowType int,
	@Lang int,
	@HitCount int,
	@URL nvarchar(500),
	@KeyContents nvarchar(300),
	@ReturnBody nvarchar(1024),
	@Locked nvarchar(50),
	@HitCountComments int,
	@Author nvarchar(350),
	@Check_type int
)

AS

SET NOCOUNT ON
declare @key nvarchar(250)
set @key=dbo.CMS_Contents_CreateKey(@Headline)
UPDATE [CMS_Contents]
SET [ContentLangID] = @ContentLangID,
	[ContentVersionID] = @ContentVersionID,
	[VersionID] = @VersionID,
	[ZoneID] = @ZoneID,
	[Headline] = @Headline,
	[SubHeadline] = @SubHeadline,
	[Teaser] = @Teaser,
	[Avatar] = @Avatar,
	[AvatarTeaser] = @AvatarTeaser,
	[SmallAvatar] = @SmallAvatar,
	[BigAvatar] = @BigAvatar,
	[AttachFile] = @AttachFile,	
	[Body] = @Body,
	[PrimaryAuthor] = @PrimaryAuthor,
	[SecondaryAuthors] = @SecondaryAuthors,		
	[ModifiedBy] = @ModifiedBy,
	[ModifiedOn] = @ModifiedOn,
	[DeletedOn] = @DeletedOn,
	[DeletedBy] = @DeletedBy,
	[DatetimePublish] = @DatetimePublish,
	[UserPublish] = @UserPublish,
	[DatetimeRemove] = @DatetimeRemove,
	[UserRemove] = @UserRemove,
	[ShowType] = @ShowType,
	[Lang] = @Lang,
	[HitCount] = @HitCount,
	[URL] = @URL,
	[KeyContents] = @key,
	[ReturnBody] = @ReturnBody,
	[Locked] = @Locked,
	[HitCountComments] = @HitCountComments,
	[Author] = @Author,
	[Check_type] = @Check_type
WHERE [ContentID] = @ContentID

GO

/****** Object:  StoredProcedure [dbo].[CMS_Contents_Delete]    Script Date: 06/25/2012 23:20:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[CMS_Contents_Delete]
(
	@ContentID int
)

AS

SET NOCOUNT ON

DELETE FROM [CMS_Contents]
WHERE [ContentID] = @ContentID


GO

/****** Object:  StoredProcedure [dbo].[CMS_Contents_Insert]    Script Date: 06/25/2012 23:20:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[CMS_Contents_Insert]
(
	@ContentLangID int,
	@ContentVersionID int,
	@VersionID int,
	@ZoneID int,
	@Headline nvarchar(250),
	@SubHeadline nvarchar(250),
	@Teaser nvarchar(3000),
	@Avatar nvarchar(350),
	@AvatarTeaser nvarchar(250),
	@SmallAvatar nvarchar(350),
	@BigAvatar nvarchar(350),
	@AttachFile nvarchar(350),
	@Status int,
	@Body ntext,
	@PrimaryAuthor int,
	@SecondaryAuthors nvarchar(350),
	@CreatedBy int,
	@CreatedOn datetime,
	@ModifiedBy int,
	@ModifiedOn datetime,
	@DeletedOn datetime,
	@DeletedBy int,
	@DatetimePublish datetime,
	@UserPublish int,
	@DatetimeRemove datetime,
	@UserRemove int,
	@ShowType int,
	@Lang int,
	@HitCount int,
	@URL nvarchar(500),
	@KeyContents nvarchar(300),
	@ReturnBody nvarchar(1024),
	@Locked nvarchar(50),
	@HitCountComments int,
	@Author nvarchar(350),
	@Check_type int
)

AS
declare @key nvarchar(250)
set @key=dbo.CMS_Contents_CreateKey(@Headline)
SET NOCOUNT ON
INSERT INTO [CMS_Contents]
(
	[ContentLangID],
	[ContentVersionID],
	[VersionID],
	[ZoneID],
	[Headline],
	[SubHeadline],
	[Teaser],
	[Avatar],
	[AvatarTeaser],
	[SmallAvatar],
	[BigAvatar],
	[AttachFile],
	[Status],
	[Body],
	[PrimaryAuthor],
	[SecondaryAuthors],
	[CreatedBy],
	[CreatedOn],
	[ModifiedBy],
	[ModifiedOn],
	[DeletedOn],
	[DeletedBy],
	[DatetimePublish],
	[UserPublish],
	[DatetimeRemove],
	[UserRemove],
	[ShowType],
	[Lang],
	[HitCount],
	[URL],
	[KeyContents],
	[ReturnBody],
	[Locked],
	[HitCountComments],
	[Author],
	[Check_type]
)
VALUES
(
	@ContentLangID,
	@ContentVersionID,
	@VersionID,
	@ZoneID,
	@Headline,
	@SubHeadline,
	@Teaser,
	@Avatar,
	@AvatarTeaser,
	@SmallAvatar,
	@BigAvatar,
	@AttachFile,
	@Status,
	@Body,
	@PrimaryAuthor,
	@SecondaryAuthors,
	@CreatedBy,
	@CreatedOn,
	@ModifiedBy,
	@ModifiedOn,
	@DeletedOn,
	@DeletedBy,
	@DatetimePublish,
	@UserPublish,
	@DatetimeRemove,
	@UserRemove,
	@ShowType,
	@Lang,
	@HitCount,
	@URL,
	@key,
	@ReturnBody,
	@Locked,
	@HitCountComments,
	@Author,
	@Check_type
)
declare @id int
set @id=SCOPE_IDENTITY()
SELECT @id
update CMS_Contents set ContentLangID=@id where ContentID=@id


GO

/****** Object:  StoredProcedure [dbo].[CMS_Contents_Type_Latest]    Script Date: 06/25/2012 23:20:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



Create PROCEDURE [dbo].[CMS_Contents_Type_Latest]		
	@TopN int=null,
	@ZoneID int=null,
	@Status int=null,	
	@ShowType nvarchar(255)=null,		
	@lang int=null,
	@type int
AS
	DECLARE @sql varchar(1000)
	DECLARE @Select varchar(500)
	DECLARE @WhereClause NVARCHAR(500)
	DECLARE @OrderClause NVARCHAR(500)
	if(@type=1)-- lấy dữ liệu 2 bảng, cms_zone, cms_Contents
		BEGIN	
			SET @Select='SELECT TOP ' + CAST(@TopN AS VARCHAR) + ' CMS_Contents.ZoneID,CMS_Contents.KeyContents,CMS_Contents.ModifiedOn, CMS_Contents.Headline,CMS_Contents.ContentID,CMS_Contents.URL,CMS_Contents.Teaser,CMS_Contents.Avatar,CMS_Contents.BigAvatar,CMS_Contents.SmallAvatar,CMS_Zones.ZoneURL,CMS_Zones.modulemenu FROM CMS_Contents,CMS_Zones '
			SET @WhereClause=' WHERE CMS_Zones.ZoneID=CMS_Contents.ZoneID AND CMS_Contents.Status=' + CAST(@Status AS VARCHAR)
			SET @WhereClause=@WhereClause  + ' AND CMS_Contents.Lang=' + CAST(@Lang AS VARCHAR)					
			if(@ShowType<>'')
				SET @WhereClause=@WhereClause  + ' AND CMS_Contents.ShowType & ' + CAST(@ShowType AS VARCHAR)+' = ' + CAST(@ShowType AS VARCHAR)		
			if(@ZoneID>0)
				SET @WhereClause=@WhereClause  + ' AND CMS_Contents.ZoneID=' + CAST(@ZoneID AS VARCHAR)	
			SET @OrderClause = ' ORDER BY CMS_Contents.ModifiedOn DESC '
			SET @Sql =@Select + @WhereClause + @OrderClause	
		END	
	if(@type=2)-- lấy dữ liệu 1 bảng cms_Contents
		BEGIN	
			SET @Select='SELECT TOP ' + CAST(@TopN AS VARCHAR) + ' CMS_Contents.ZoneID,CMS_Contents.KeyContents,CMS_Contents.ModifiedOn, CMS_Contents.Headline,CMS_Contents.ContentID,CMS_Contents.URL,CMS_Contents.Body FROM CMS_Contents '
			SET @WhereClause=' WHERE CMS_Contents.Status=' + CAST(@Status AS VARCHAR)
			SET @WhereClause=@WhereClause  + ' AND CMS_Contents.Lang=' + CAST(@Lang AS VARCHAR)					
			if(@ShowType<>'')
				SET @WhereClause=@WhereClause  + ' AND CMS_Contents.ShowType & ' + CAST(@ShowType AS VARCHAR)+' = ' + CAST(@ShowType AS VARCHAR)		
			if(@ZoneID>0)
				SET @WhereClause=@WhereClause  + ' AND CMS_Contents.ZoneID=' + CAST(@ZoneID AS VARCHAR)	
			SET @OrderClause = ' ORDER BY CMS_Contents.ModifiedOn DESC '
			SET @Sql =@Select + @WhereClause + @OrderClause	
		END	
	execute(@Sql)



GO

/****** Object:  StoredProcedure [dbo].[CMS_Contents_Select]    Script Date: 06/25/2012 23:20:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[CMS_Contents_Select]
@Type INT,
@Para_1 NVARCHAR(100) = NULL,
@Para_2 NVARCHAR(100) = NULL
AS
BEGIN	
	IF(@Type = 1) 
		BEGIN
			SELECT  * 
			FROM	CMS_Contents								
		END	
	IF(@Type = 2) 
		BEGIN
			SELECT * FROM CMS_Contents
			WHERE ContentID = CAST(@Para_1 AS INT)						
		END		
			
END

GO


